The library cache advisory provides assistance in right sizing the Oracle shared pool.

The library cache stores complex object data associated with cursor. It also stores SQL and PL/SQL statements so that users can share them. It allows for cursor sharing

Each of these types of data associated with the cursors has a namespace assigned to the allocation. For example, the sqlarea shows all of the allocation for SQL and PL/SQL executable code.

The statistics related to the namespace are:

• Get Requests:The number of requests for information about the relevant item.
• Pins:The number of executions of SQL statements or procedures.
• ReloadsThe number of times - during the execution phase - that a parsed representation of an SQL statement in the shared SQL area is aged-out to make space for another statement.
• InvalidationsThe number of invalid statements due to changes of a dependent object.

Invalidations are caused by:

• DDL statements
• Gathering of statistics against dependent objects

Note: Invalidations also cause reloads.

Pct Misses should be very low.

In the example, the Get Pct Miss is 3.12% and this is at a fair level and so is the Pin Pct Miss at 0.82%.

This indicates that the shared pool is adequately sized; however, the Shared Pool Advisor may show that changing the size of the shared pool will benefit performance.

In case of Automatic Memory Management (AMM), the DBA can increase the size of the SGA component; otherwise, increase the value of the database initialization parameter SHARED_POOL_SIZE.